﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Model.Data;
using Wedn.Utility;

namespace Wedn.DAL
{
    /// <summary>
    /// 评论表表操作类(初步审核通过)
    /// 此类为抽象类, 不允许被实例化, 需要使用时直接调用
    /// </summary>
    public abstract class CommentsDAO
    {
        #region 增加评论
        /// <summary>
        /// 添加一个新的评论 返回是否添加成功
        /// </summary>
        /// <param name="comment">要添加的评论对象</param>
        /// <returns>受影响的行数</returns>
        public static int Insert(Comment comment)
        {
            string sql = @"INSERT INTO [Blog_Comments]
                                    (
                                        [comment_post_id],
                                        [comment_auther],
                                        [comment_auther_email],
                                        [comment_auther_url],
                                        [comment_auther_ip],
                                        [comment_pubdate],
                                        [comment_content],
                                        [comment_status],
                                        [comment_parent_id]
                                    )
                                    VALUES
                                    (
                                        @PostID,
                                        @Auther,
                                        @AutherEmail,
                                        @AutherUrl,
                                        @AutherIP,
                                        @PubDate,
                                        @Content,
                                        @Status,
                                        @ParentId
                                    );";
            return ExecuteNonQuery(comment, sql);
        }
        #endregion

        #region 删除评论
        /// <summary>
        /// 根据评论ID删除数据库评论表中的评论对象
        /// </summary>
        /// <param name="commentid">要删除的评论ID</param>
        /// <returns>删除是否成功</returns>
        public static int Delete(int commentid)
        {
            string sql = "DELETE FROM [Blog_Comments] WHERE [comment_id] = @Id;";
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Id", commentid));
        }
        #endregion

        #region 修改评论
        /// <summary>
        /// 根据评论对象的ID属性更新评论信息
        /// </summary>
        /// <param name="comment">要更新的评论对象</param>
        /// <returns>返回受影响行数</returns>
        public static int Update(Comment comment)
        {
            string sql = @"UPDATE [Blog_Comments]
                                   SET
                                        [comment_post_id]=@PostID,
                                        [comment_auther]=@Auther,
                                        [comment_auther_email]=@AutherEmail,
                                        [comment_auther_url]=@AutherUrl,
                                        [comment_auther_ip]=@AutherIP,
                                        [comment_pubdate]=@PubDate,
                                        [comment_content]=@Content,
                                        [comment_status]=@Status,
                                        [comment_parent_id]=@ParentId
                                    WHERE 
                                        [comment_id]=@Id";
            return ExecuteNonQuery(comment, sql);
        }
        #endregion

        #region 选取评论

        #region 获取表中主键最大值
        /// <summary>
        /// 获取表中主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public static int GetMaxId()
        {
            return SqlHelper.SelectMaxId("comment_id", "Blog_Comments");
        }
        #endregion

        #region 获取所有评论
        /// <summary>
        /// 获取评论表中所有评论信息, 返回对象集合
        /// </summary>
        /// <returns>所有评论信息集合</returns>
        public static List<Comment> SelectComments()
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = @"SELECT
                                        [comment_id],
                                        [comment_post_id],
                                        [comment_auther],
                                        [comment_auther_email],
                                        [comment_auther_url],
                                        [comment_auther_ip],
                                        [comment_pubdate],
                                        [comment_content],
                                        [comment_status],
                                        [comment_parent_id]
                                    FROM [Blog_Comments]";
            return Select(sql);
        }
        #endregion

        #region 根据指定字段和是否降序排序从文章信息表中获取指定条数文章信息, 返回对象集合
        /// <summary>
        /// 根据指定字段和是否降序排序从评论表中获取指定条数评论信息, 返回对象集合
        /// </summary>
        /// <param name="pageSize">选择数目</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="isDescending">是否降序</param>
        /// <returns>符合条件评论信息集合</returns>
        public static List<Comment> SelectComments(int pageSize, string orderField, bool isDescending)
        {
            string sql = string.Format(@"SELECT TOP {0}
                                                                [comment_id],
                                                                [comment_post_id],
                                                                [comment_auther],
                                                                [comment_auther_email],
                                                                [comment_auther_url],
                                                                [comment_auther_ip],
                                                                [comment_pubdate],
                                                                [comment_content],
                                                                [comment_status],
                                                                [comment_parent_id]
                                                           FROM [Blog_Comments]
                                                           ORDER BY
                                                                {1}", pageSize, orderField);
            if (isDescending)
                sql += " DESC";
            return Select(sql);
        }
        #endregion

        #region 根据文章ID获取评论集合
        /// <summary>
        /// 根据文章ID获取属于该文章的所有评论, 返回对象集合
        /// </summary>
        /// <param name="postId">文章ID</param>
        /// <returns>评论信息集合</returns>
        public static List<Comment> SelectComments(int postId)
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = @"SELECT
                                        [comment_id],
                                        [comment_post_id],
                                        [comment_auther],
                                        [comment_auther_email],
                                        [comment_auther_url],
                                        [comment_auther_ip],
                                        [comment_pubdate],
                                        [comment_content],
                                        [comment_status],
                                        [comment_parent_id]
                                    FROM
                                        [Blog_Comments]
                                    WHERE
                                        [comment_post_id]=@PostId";
            SqlParameter parameter = new SqlParameter("@PostId", postId);
            return Select(sql, parameter);
        }
        #endregion

        #region 根据评论ID获取评论对象
        /// <summary>
        /// 根据评论ID获取评论表中所有评论对象, 返回评论对象
        /// </summary>
        /// <param name="commentid">指定ID</param>
        /// <returns>所有评论信息集合</returns>
        public static Comment SelectComment(int commentid)
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = @"SELECT
                                        [comment_id],
                                        [comment_post_id],
                                        [comment_auther],
                                        [comment_auther_email],
                                        [comment_auther_url],
                                        [comment_auther_ip],
                                        [comment_pubdate],
                                        [comment_content],
                                        [comment_status],
                                        [comment_parent_id]
                                    FROM
                                        [Blog_Comments]
                                     WHERE
                                        [comment_id]=@Id;";
            return Select(sql, new SqlParameter("@Id", commentid))[0];
        }
        #endregion

        #region 选取评论总数
        /// <summary>
        /// 选取评论总数
        /// </summary>
        /// <returns>总数</returns>
        public static int SelectCommentsCount()
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = @"SELECT
                                    COUNT
                                    (
                                        [comment_id]
                                    )
                                   FROM
                                        [Blog_Comments];";
            return (int)SqlHelper.ExecuteScalar(sql);
        }
        #endregion

        #endregion

        #region 本类私有方法

        #region ExecuteNonQuery
        /// <summary>
        /// 执行写入数据库操作
        /// </summary>
        /// <param name="comment">要写入数据库的对象</param>
        /// <param name="sql">数据库的写入语句</param>
        /// <returns>返回受影响行数</returns>
        private static int ExecuteNonQuery(Comment comment, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                   new SqlParameter("@PostID", comment.PostId),
                   new SqlParameter("@Auther", comment.Auther),
                   new SqlParameter("@AutherEmail", string.IsNullOrEmpty(comment.AutherEmail) ? DBNull.Value : (object)comment.AutherEmail),
                   new SqlParameter("@AutherUrl", string.IsNullOrEmpty(comment.AutherUrl) ? DBNull.Value : (object)comment.AutherUrl),
                   new SqlParameter("@AutherIP", string.IsNullOrEmpty(comment.AutherIp) ? DBNull.Value : (object)comment.AutherIp),
                   new SqlParameter("@PubDate", comment.PubDate),
                   new SqlParameter("@Content", comment.Content),
                   new SqlParameter("@Status", comment.Status),
                   new SqlParameter("@ParentId", comment.ParentId),
                   new SqlParameter("@Id",comment.Id));
        }
        #endregion

        #region Select
        /// <summary>
        /// 选取数据集合方法
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameter">参数列表</param>
        /// <returns>数据集合</returns>
        private static List<Comment> Select(string sql, params SqlParameter[] parameter)
        {
            List<Comment> list = new List<Comment>();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                while (reader.Read())
                {
                    Comment comment = new Comment(
                        reader.GetInt32(0),
                        reader.GetInt32(1),
                        reader.GetString(2),
                        reader.IsDBNull(3) ? null : reader.GetString(3),
                        reader.IsDBNull(4) ? null : reader.GetString(4),
                        reader.IsDBNull(5) ? null : reader.GetString(5),
                        reader.GetDateTime(6).ToString(),
                        reader.GetString(7),
                        reader.GetInt32(8),
                        reader.GetInt32(9));
                    list.Add(comment);
                }
            }
            return list;
        }
        #endregion

        #endregion
    }
}
